/*
 exec SP_CREATE_WIP_FLUSH_SETUP 0
 select * from WIp_Flush_Table where processStep_id = 1549
*/
ALTER PROC SP_CREATE_WIP_FLUSH_SETUP( @NewProcessStep_Id INT )
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @PSCursor CURSOR
	DECLARE @ProcessStep_ID INT
	DECLARE @NEXT_PROCESSSTEP_ID INT
	DECLARE @PRODUCT_ID INT
	DECLARE @PATH_NUMBER INT
	DECLARE @PREV_PATH_NUMBER INT
	DECLARE @ROW_ID INT
	DECLARE @PS_TYPE_NAME VARCHAR(50)
	DECLARE @TOTAL_CYCLE_TIME INT
	DECLARE @TRANSIT_TIME INT
	DECLARE @FINAL_YIELD NUMERIC( 12,4 )
	DECLARE @YIELD NUMERIC( 5,2 )
	DECLARE @STD_CYCLE_TIME FLOAT
	DECLARE @CURRENT_YEAR INT
	DECLARE @CURRENT_QTR INT
	DECLARE @FINAL_YIELD_TIME_CURSOR CURSOR
	DECLARE @GROSS_DIE_FACTOR FLOAT
	DECLARE @CURRENT_PROCESS_CYCLE_TIME INT
	DECLARE @CURRENT_PROCESS_TRANSIT_TIME INT
	DECLARE @DB_FINAL_YIELD NUMERIC( 12,4 )
	DECLARE @DB_TOTAL_CYCLE_TIME INT
	DECLARE @MAX_PATH_NUMBER INT
	DECLARE @CONSIDER_DIES_PER_PACKAGE INT	
	DECLARE @DIES_PER_PACKAGE INT

	SET @CURRENT_YEAR = YEAR( GETDATE() )
	SET @CURRENT_QTR  = DATEPART( Q, GETDATE() )
	SET @STD_CYCLE_TIME = 0

	DECLARE @PATHS_TABLE TABLE(ROW_ID INT, PATH_NUMBER INT, PROCESSSTEP_ID INT, PRODUCT_ID INT, PS_TYPE VARCHAR(50), PS_TYPE_NAME VARCHAR(50) )
	DECLARE @FINAL_YIELD_CYCLE_TIME_TABLE TABLE ( PATH_NUMBER INT, ProcessStep_Id INT, FINAL_PROCESSSTEP_ID INT, FINAL_PRODUCT_ID INT, FINAL_PS_TYPE_NAME VARCHAR(50), FINAL_YIELD NUMERIC(8,4), TOTAL_CYCLE_TIME INT, GROSS_DIE_FACTOR FLOAT, CURRENT_PROCESS_CYCLE_TIME INT, CURRENT_PROCESS_TRANSIT_TIME INT)

	IF @NewProcessStep_Id = 0
	BEGIN
		SET @PSCursor = CURSOR FOR SELECT DISTINCT LT.ProcessStep_Id FROM Lot_Table LT, Process_Step PS WHERE LT.ProcessStep_Id = PS.ProcessStep_Id AND ( Lot_Current_Status = 'Running' OR Lot_Current_Status = 'Hold' ) AND PS.PS_Type_Name <> 'Finished Goods'
	END
	ELSE
	BEGIN
		SET @PSCursor = CURSOR FOR SELECT ProcessStep_Id FROM Process_Step WHERE ProcessStep_ID = @NewProcessStep_Id
	END
	OPEN @PSCursor
	FETCH NEXT FROM @PSCursor INTO @ProcessStep_Id 
	WHILE @@FETCH_STATUS = 0
	BEGIN
		INSERT INTO @PATHS_TABLE 
			SELECT ROW_ID, PATH_NUMBER, PROCESSSTEP_ID, PRODUCT_ID, PS_TYPE, PS_TYPE_NAME FROM FN_GET_PROCESS_FLOWS_BY_PROCESSSTEP_ID (@PROCESSSTEP_ID,1) ORDER BY PATH_NUMBER, ROW_ID

		SELECT @MAX_PATH_NUMBER = MAX(PATH_NUMBER) FROM @PATHS_TABLE

		SELECT TOP 1 @ROW_ID = ROW_ID, @PATH_NUMBER = PATH_NUMBER, @NEXT_PROCESSSTEP_ID = PROCESSSTEP_ID, @PRODUCT_ID = PRODUCT_ID, @PS_TYPE_NAME = PS_TYPE_NAME FROM @PATHS_TABLE
		SET @GROSS_DIE_FACTOR = 1
		SET @CONSIDER_DIES_PER_PACKAGE = 1 --DO NOT CONSIDER DIES/PACKAGE
		IF( @PS_TYPE_NAME = 'FAB' OR @PS_TYPE_NAME = 'WAFER STORE' OR @PS_TYPE_NAME = 'SORT' )
		BEGIN
			SELECT @GROSS_DIE_FACTOR = ISNULL( Parameter_Value, 1 ) FROM PROCESS_PARAMETER WHERE ProcessStep_ID = (select TOP 1 ProcessStep_Id from FN_GET_PROCESS_FLOW_By_ProcessStep_Id ( @ProcessStep_Id ) WHERE Process_Type_Name = 'FAB' ) AND Parameter_Key = 'Gross Die/Wafer'
			SET @CONSIDER_DIES_PER_PACKAGE = 0 --CONSIDER DIES/PACKAGE AS WE ARE CALCULATING PATH FROM BEFORE ASSEMBLY
		END
		IF ( @GROSS_DIE_FACTOR IS NULL OR @GROSS_DIE_FACTOR = 0 )
		 BEGIN
			SET @GROSS_DIE_FACTOR = 1
		 END
		SELECT @CURRENT_PROCESS_CYCLE_TIME = ISNULL( PARAMETER_VALUE, 0 ) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @ProcessStep_Id
		SET @CURRENT_PROCESS_CYCLE_TIME = ( CASE WHEN @CURRENT_PROCESS_CYCLE_TIME IS NULL THEN 0 ELSE @CURRENT_PROCESS_CYCLE_TIME END )
		SELECT @CURRENT_PROCESS_TRANSIT_TIME = ISNULL( TT_NORMAL_TIME, 0 ) FROM TRANSPORTATION_TABLE WHERE PROCESSSTEP_ID = @ProcessStep_Id
		SET @CURRENT_PROCESS_TRANSIT_TIME = ( CASE WHEN @CURRENT_PROCESS_TRANSIT_TIME IS NULL THEN 0 ELSE @CURRENT_PROCESS_TRANSIT_TIME END )
		INSERT INTO @FINAL_YIELD_CYCLE_TIME_TABLE ( PATH_NUMBER, ProcessStep_Id, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, FINAL_PS_TYPE_NAME, FINAL_YIELD, TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME ) VALUES ( @PATH_NUMBER, @ProcessStep_Id, @NEXT_PROCESSSTEP_ID, @PRODUCT_ID, @PS_TYPE_NAME, 100.00, 0, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME )
		SET @PREV_PATH_NUMBER = @PATH_NUMBER
		WHILE ( @ROW_ID > 0 )
		BEGIN
			SET @TOTAL_CYCLE_TIME = 0
			SET @FINAL_YIELD = 0
			SET @TRANSIT_TIME = 0
			SET @YIELD = 100.00
			SET @DIES_PER_PACKAGE = 1
			IF ( @PATH_NUMBER != @PREV_PATH_NUMBER )
			BEGIN
				INSERT INTO @FINAL_YIELD_CYCLE_TIME_TABLE ( PATH_NUMBER, ProcessStep_Id, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, FINAL_PS_TYPE_NAME, FINAL_YIELD, TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME ) VALUES ( @PATH_NUMBER, @ProcessStep_Id, @NEXT_PROCESSSTEP_ID, @PRODUCT_ID, @PS_TYPE_NAME, 100.00, 0, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME )		
				SET @PREV_PATH_NUMBER = @PATH_NUMBER
			END

			SELECT @TOTAL_CYCLE_TIME = TOTAL_CYCLE_TIME, @FINAL_YIELD = FINAL_YIELD FROM @FINAL_YIELD_CYCLE_TIME_TABLE WHERE PATH_NUMBER = @PATH_NUMBER

			SELECT @STD_CYCLE_TIME = ISNULL( PARAMETER_VALUE, 0 ) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Standard Cycle Time' AND PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
			SET @TOTAL_CYCLE_TIME = @TOTAL_CYCLE_TIME + ISNULL( @STD_CYCLE_TIME, 0 )
			SELECT @TRANSIT_TIME = ISNULL( TT_NORMAL_TIME, 0 ) FROM TRANSPORTATION_TABLE WHERE PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
			SET @TOTAL_CYCLE_TIME = @TOTAL_CYCLE_TIME + ISNULL( @TRANSIT_TIME, 0 )
			SELECT @YIELD = CASE WHEN @CURRENT_QTR = 1 THEN ISNULL ( Q1_YIELD, 100 ) WHEN @CURRENT_QTR = 2 THEN ISNULL ( Q2_YIELD, 100) WHEN @CURRENT_QTR = 3 THEN ISNULL ( Q3_YIELD, 100) WHEN @CURRENT_QTR = 4 THEN ISNULL ( Q4_YIELD, 100) END FROM UPDATE_VIEW_COST_TABLE WHERE PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID AND YEAR(FIN_YEAR) = @CURRENT_YEAR
			SET @YIELD = ( CASE WHEN @YIELD IS NULL OR @YIELD = 0.0 THEN 100.00 ELSE @YIELD END )

			IF ( @PS_TYPE_NAME = 'ASSEMBLY' AND @CONSIDER_DIES_PER_PACKAGE = 0 )
			 BEGIN
				SELECT @DIES_PER_PACKAGE = ISNULL( PARAMETER_VALUE, 1 ) FROM PROCESS_PARAMETER WHERE PARAMETER_KEY = 'Dies/Package' AND PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID
				IF ( @DIES_PER_PACKAGE IS NULL OR @DIES_PER_PACKAGE = 0 )
				 BEGIN
					SET @DIES_PER_PACKAGE = 1
				 END
				SET @FINAL_YIELD = @FINAL_YIELD/@DIES_PER_PACKAGE
			 END

			SET @FINAL_YIELD = ( @FINAL_YIELD * @YIELD )/100
			UPDATE @FINAL_YIELD_CYCLE_TIME_TABLE SET TOTAL_CYCLE_TIME = @TOTAL_CYCLE_TIME, FINAL_YIELD = @FINAL_YIELD, FINAL_PROCESSSTEP_ID = @NEXT_PROCESSSTEP_ID, FINAL_PRODUCT_ID = @PRODUCT_ID, FINAL_PS_TYPE_NAME = @PS_TYPE_NAME WHERE PATH_NUMBER = @PATH_NUMBER
			DELETE FROM @PATHS_TABLE WHERE ROW_ID = @ROW_ID
	
			IF ( @PS_TYPE_NAME = 'FINISHED GOODS' )
			 BEGIN
				SET @PATH_NUMBER = NULL
				SELECT TOP 1 @PATH_NUMBER = PATH_NUMBER FROM @PATHS_TABLE
				IF ( @PATH_NUMBER IS NOT NULL AND @PREV_PATH_NUMBER = @PATH_NUMBER ) --THERE IS A FG IN THE MIDDLE OF THE PATH, WE NEED TO CONSIDER THIS FG ALSO
				 BEGIN
					SET @MAX_PATH_NUMBER = @MAX_PATH_NUMBER + 1
					INSERT INTO @FINAL_YIELD_CYCLE_TIME_TABLE ( PATH_NUMBER, ProcessStep_Id, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, FINAL_PS_TYPE_NAME, FINAL_YIELD, TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME ) 
						VALUES ( @MAX_PATH_NUMBER, @ProcessStep_Id, @NEXT_PROCESSSTEP_ID, @PRODUCT_ID, @PS_TYPE_NAME, @FINAL_YIELD, @TOTAL_CYCLE_TIME, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME )
				 END
			 END

			SET @ROW_ID = 0
			SET @PATH_NUMBER = 0
			SET @NEXT_PROCESSSTEP_ID = NULL
			SET @PS_TYPE_NAME = NULL
			SET @STD_CYCLE_TIME = 0
			SET @TOTAL_CYCLE_TIME = 0
			SET @TRANSIT_TIME = 0
			SELECT TOP 1 @ROW_ID = ROW_ID, @PATH_NUMBER = PATH_NUMBER, @NEXT_PROCESSSTEP_ID = PROCESSSTEP_ID, @PRODUCT_ID = PRODUCT_ID, @PS_TYPE_NAME = PS_TYPE_NAME FROM @PATHS_TABLE
			
		END
		--INSERT INTO WIP_Flush_Table SELECT ProcessStep_Id, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, QTY_OUT, EXP_DATE_OUT, Current_Process_Cycle_Time FROM @FINAL_YIELD_CYCLE_TIME_TABLE WHERE Final_PS_Type_Name = 'FINISHED GOODS'
		DECLARE @CURR_PROCESSSTEP_ID INT
		DECLARE @FG_PROCESSSTEP_ID INT
		DECLARE @FG_PRODUCT_ID INT
	
		SET @FINAL_YIELD_TIME_CURSOR = CURSOR FOR SELECT ProcessStep_Id, FINAL_PROCESSSTEP_ID, FINAL_PRODUCT_ID, FINAL_YIELD, TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME FROM @FINAL_YIELD_CYCLE_TIME_TABLE WHERE Final_PS_Type_Name = 'FINISHED GOODS'

		DELETE FROM WIP_FLUSH_TABLE WHERE PROCESSSTEP_ID = @ProcessStep_Id
		OPEN @FINAL_YIELD_TIME_CURSOR		
		FETCH NEXT FROM @FINAL_YIELD_TIME_CURSOR INTO @CURR_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @FG_PRODUCT_ID, @FINAL_YIELD, @TOTAL_CYCLE_TIME, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME
		WHILE @@FETCH_STATUS = 0
		 BEGIN
			SELECT @DB_FINAL_YIELD = FINAL_YIELD, @DB_TOTAL_CYCLE_TIME = TOTAL_CYCLE_TIME FROM WIP_Flush_Table WHERE PROCESSSTEP_ID = @CURR_PROCESSSTEP_ID AND FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID

			IF ( @DB_TOTAL_CYCLE_TIME IS NULL  ) --INSERT
			 BEGIN
				INSERT INTO WIP_Flush_Table(PROCESSSTEP_ID, FG_PROCESSSTEP_ID, FG_PRODUCT_ID, FINAL_YIELD, TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME)
					VALUES(@CURR_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @FG_PRODUCT_ID, @FINAL_YIELD, @TOTAL_CYCLE_TIME, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME)
			 END
			ELSE IF ( @TOTAL_CYCLE_TIME > @DB_TOTAL_CYCLE_TIME )--UPDATE
			 BEGIN
				UPDATE WIP_Flush_Table SET FINAL_YIELD = @FINAL_YIELD, TOTAL_CYCLE_TIME = @TOTAL_CYCLE_TIME, GROSS_DIE_FACTOR = @GROSS_DIE_FACTOR, CURRENT_PROCESS_CYCLE_TIME = @CURRENT_PROCESS_CYCLE_TIME, CURRENT_PROCESS_TRANSIT_TIME = @CURRENT_PROCESS_TRANSIT_TIME WHERE PROCESSSTEP_ID = @CURR_PROCESSSTEP_ID AND FG_PROCESSSTEP_ID = @FG_PROCESSSTEP_ID
			 END
			SET @DB_FINAL_YIELD = NULL
			SET @DB_TOTAL_CYCLE_TIME = NULL
			SET @CURR_PROCESSSTEP_ID = NULL
			SET @FG_PROCESSSTEP_ID = NULL
			SET @FG_PRODUCT_ID = NULL
			SET @FINAL_YIELD = 0.0
			SET @TOTAL_CYCLE_TIME = 0
			SET @GROSS_DIE_FACTOR = 1
			SET @CURRENT_PROCESS_CYCLE_TIME = 0
			SET @CURRENT_PROCESS_TRANSIT_TIME = 0
			FETCH NEXT FROM @FINAL_YIELD_TIME_CURSOR INTO @CURR_PROCESSSTEP_ID, @FG_PROCESSSTEP_ID, @FG_PRODUCT_ID, @FINAL_YIELD, @TOTAL_CYCLE_TIME, @GROSS_DIE_FACTOR, @CURRENT_PROCESS_CYCLE_TIME, @CURRENT_PROCESS_TRANSIT_TIME
		 END
		CLOSE @FINAL_YIELD_TIME_CURSOR
		DEALLOCATE @FINAL_YIELD_TIME_CURSOR
		DELETE FROM @FINAL_YIELD_CYCLE_TIME_TABLE

		FETCH NEXT FROM @PSCursor INTO @ProcessStep_Id 
	END
	CLOSE @PSCursor
	DEALLOCATE @PSCursor
END